package com.shanghai.shanghaielectric.excel;

import android.util.Log;

import com.shanghai.shanghaielectric.Constants;
import com.shanghai.shanghaielectric.Model.CapacityCuttingModel;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * Created by 45000460 on 2017/12/27.
 */

public class ExcelHelper {
    private static final List<String> stringList_capacityCutting = Arrays.asList("时间", "共补1#投切次数", "共补2#投切次数", "共补3#投切次数", "共补4#投切次数", "分补1#A投切次数", "分补1#B投切次数", "分补1#C投切次数", "分补2#A投切次数", "分补2#B投切次数", "分补2#C投切次数", "分补3#A投切次数", "分补3#B投切次数", "分补3#C投切次数");
    private static final List<String> stringList_reportData = Arrays.asList(
            "时间",
            "总功率因数最大值时间",
            "总功率因数最小值时间",

            "总功率因数最大值",
            "总功率因数最小值",
            "总功率因数平均值",

            "系统侧A相功率因数最大值",
            "系统侧A相功率因数最小值",
            "系统侧A相功率因数平均值",

            "系统侧B相功率因数最大值",
            "系统侧B相功率因数最小值",
            "系统侧B相功率因数平均值",

            "系统侧C相功率因数最大值",
            "系统侧C相功率因数最小值",
            "系统侧C相功率因数平均值",

            "负载侧A相功率因数最大值",
            "负载侧A相功率因数最小值",
            "负载侧A相功率因数平均值",
            "负载侧B相功率因数最大值",
            "负载侧B相功率因数最小值",
            "负载侧B相功率因数平均值",
            "负载侧C相功率因数最大值",
            "负载侧C相功率因数最小值",
            "负载侧C相功率因数平均值",

            "系统侧A相有功功率最大值",
            "系统侧A相有功功率最小值",
            "系统侧A相有功功率平均值",
            "系统侧B相有功功率最大值",
            "系统侧B相有功功率最小值",
            "系统侧B相有功功率平均值",
            "系统侧C相有功功率最大值",
            "系统侧C相有功功率最小值",
            "系统侧C相有功功率平均值",

            "系统侧A相无功功率最大值",
            "系统侧A相无功功率最小值",
            "系统侧A相无功功率平均值",
            "系统侧B相无功功率最大值",
            "系统侧B相无功功率最小值",
            "系统侧B相无功功率平均值",
            "系统侧C相无功功率最大值",
            "系统侧C相无功功率最小值",
            "系统侧C相无功功率平均值",
            "装置运行事件"
            , "共补1#投切次数", "共补2#投切次数", "共补3#投切次数", "共补4#投切次数", "分补1#A投切次数", "分补1#B投切次数", "分补1#C投切次数", "分补2#A投切次数", "分补2#B投切次数", "分补2#C投切次数", "分补3#A投切次数", "分补3#B投切次数", "分补3#C投切次数"
    );


    public static void insertCapacitorCuttingRecord() {
//所有数据导出到excel报表中
//        DataSupport.findAllAsync(CapacityCuttingModel.class).listen(new FindMultiCallback() {
//            @Override
//            public <T> void onFinish(List<T> t) {
//                try {
//
//                    writeDataToExcel(Constants.REPORT_PATH+"/投切记录.xls", "电容投切记录", 1, (List<CapacityCuttingModel>) t,stringList_capacityCutting);
//                } catch (Exception e) {
//                    e.printStackTrace();
//                }
//            }
//        });

        try {
            List<List<String>> lists1=new ArrayList<>();
            lists1=getStringList_capacityCutting(test());
            lists1.add(0,stringList_capacityCutting);

            List<List<String>> lists2=new ArrayList<>();
            lists2.add(0,stringList_reportData);

            writeDataToExcel(Constants.REPORT_PATH + "/数据报表.xls", "电容投切记录","数据", lists1,lists2);

//            insertReportData();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static List<CapacityCuttingModel> test() {
        List<CapacityCuttingModel> capacityCuttingModels = new ArrayList<>();


        for (int i = 0; i < 10; i++) {
            CapacityCuttingModel capacityCuttingModel = new CapacityCuttingModel();

            capacityCuttingModel.date = System.currentTimeMillis();
            capacityCuttingModel.GB_1 = i;
            capacityCuttingModel.GB_2 = i + 2;
            capacityCuttingModel.GB_3 = i;
            capacityCuttingModel.GB_4 = i;

            capacityCuttingModel.FB_1_A = i;
            capacityCuttingModel.FB_1_B = i;
            capacityCuttingModel.FB_1_C = i
            ;
            capacityCuttingModel.FB_2_A = i;
            capacityCuttingModel.FB_2_B = i;
            capacityCuttingModel.FB_2_C = i;

            capacityCuttingModel.FB_3_A = i;
            capacityCuttingModel.FB_3_B = i;
            capacityCuttingModel.FB_3_C = i;
            capacityCuttingModels.add(capacityCuttingModel);
        }
        return capacityCuttingModels;
    }

    private static List<List<String>> getStringList_capacityCutting(List<CapacityCuttingModel> capacityCuttingModels) {
        List<List<String>> stringList = new ArrayList<>();
        for (int i = 0; i < capacityCuttingModels.size(); i++) {
            stringList.add(capacityCuttingModels.get(i).getData());
        }
        return stringList;
    }


//    public static void insertReportData() throws Exception {
////所有数据导出到excel报表中
//        List<List<String>> stringList = new ArrayList<>();
//        stringList.add(stringList_reportData);
//        writeDataToExcel(Constants.REPORT_PATH+"/投切记录.xls", "数据报表", 0, stringList, 20);
//
//    }


    /**
     * 写
     *
     * @param String                     fileName 被写入的Excel文件的路径
     * @param String                     sheetName 被写入的Excel文件的工作表
     * @param int                        location 被写入的Excel文件的工作表位于工作簿的位置
     * @param List<CapacityCuttingModel> listData_capacityCutting 投切次数记录
     * @throws Exception
     */
    private static void writeDataToExcel(String fileName, String sheetName_1,String sheetName_2, List<List<String>> listData_capacityCutting, List<List<String>> listData_reportData) throws Exception {
        WritableWorkbook wwb = null;
        Label label = null;
        List<String> stringList = null;

        // 创建可写入的工作簿对象
        File file = new File(fileName);
        if (!file.exists()) {
            file.createNewFile();
        }
        wwb = Workbook.createWorkbook(file);
        if (wwb != null) {
            // 在工作簿里创建可写入的工作表，第一个参数为工作表名，第二个参数为该工作表的所在位置
            WritableSheet writableSheet = wwb.createSheet(sheetName_1, 0);
            WritableSheet writableSheet2 = wwb.createSheet(sheetName_2, 1);

//            wwb.get

            writableSheet.getSettings().setDefaultColumnWidth(15);
            writableSheet.getSettings().setHorizontalCentre(true);
            writableSheet2.getSettings().setDefaultColumnWidth(25);
            writableSheet2.getSettings().setHorizontalCentre(true);
//设置字体
            WritableCellFormat cellFormat1 = new WritableCellFormat();
//文字居中对齐
            cellFormat1.setAlignment(Alignment.CENTRE);
//设置垂直对其
            cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
            if (writableSheet != null) {
                /* 添加表结构 */
                // 行
//                for (int j = 0; j < titles.size(); j++) {
//                    // Label构造器中有三个参数，第一个为列，第二个为行，第三个则为单元格填充的内容
//                    label = new Label(j, 0, titles.get(j), cellFormat1);
//                    // 将被写入数据的单元格添加到工作表
//                    writableSheet.addCell(label);
//                }
                if (listData_capacityCutting != null) {
//第1个sheet
                    for (int i = 0; i < listData_capacityCutting.size(); i++) {
                        stringList = listData_capacityCutting.get(i);
                        // 列
                        for (int j = 0; j < stringList.size(); j++) {
                            // Label构造器中有三个参数，第一个为列，第二个为行，第三个则为单元格填充的内容
                            label = new Label(j, i, stringList.get(j), cellFormat1);
                            // 将被写入数据的单元格添加到工作表
                            writableSheet.addCell(label);

                        }
                    }
//第2个sheet
                    for (int i = 0; i < listData_reportData.size(); i++) {
                        stringList = listData_reportData.get(i);
                        // 列
                        for (int j = 0; j < stringList.size(); j++) {
                            // Label构造器中有三个参数，第一个为列，第二个为行，第三个则为单元格填充的内容
                            label = new Label(j, i, stringList.get(j), cellFormat1);
                            // 将被写入数据的单元格添加到工作表
                            writableSheet2.addCell(label);

                        }
                    }
                }

                // 从内存中写入到文件
                wwb.write();

            }
            System.out.println("路径为：" + fileName + "的工作簿写入数据成功！");
        }
        Log.e("sheet", "插入数据");
        wwb.close();

    }


}
